using System;
using System.Data;
using System.Configuration;
//using System.Windows.Forms;

using System.Text;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Text.RegularExpressions;
using Microsoft.VisualBasic;
using System.Collections;
using System.Linq;
using System.IO;
using System.ComponentModel;
using System.Web;
using Logger;
/// <summary>
/// Summary description for DBFunction
/// </summary>
public class DBClass
{
    //set debugging mode on
    public bool isLive = true;
    public bool isDebug = false;

    public enum DataType { String = 1, Number, DateTime, Image, Decimal, Bit,StringLike };
    public SqlConnection dbconn = new SqlConnection();
    SqlCommand comm = new SqlCommand();
    SqlCommand syncSqlCommand = new SqlCommand();
    public DataSet ds;
    SqlDataAdapter ada = new SqlDataAdapter();
    SqlDataReader oDataReader = null;

    public UInt32 uiMinId = 100000;
    public DBClass()
    {
        string connectionString = string.Empty;
        try
        {
            connectionString = Convert.ToString(HttpContext.Current.Application["constr"]);
            dbconn = new SqlConnection(connectionString);
        }
        catch (Exception ex)
        {
            cGlobalSettings.oLogger.WriteLogException("DBFUNCTION::DBFunction()" + string.Format("Making connection with server : {0} \t possible issue connection string key not found", connectionString), ex);
        }
        //
        // TODO: Add constructor logic here
        //
    }
    public DBClass(string connectionString)
    {

        dbconn = new SqlConnection(connectionString);

        }
    public static string getWebConfigData(string appkey)
    {
        string appKeyData = string.Empty;
        try
        {   
            if (!string.IsNullOrEmpty(Convert.ToString(ConfigurationManager.AppSettings[appkey])))
            {
                appKeyData = Convert.ToString(ConfigurationManager.AppSettings[appkey]);
            }
            else
            {
                //get from defaults
                appKeyData = string.Empty;
            }
        }
        catch (Exception ex)
        {
            //get from defaults
            appKeyData = string.Empty;
        }

        return appKeyData;
    }   

    public string DatabaseConnectionString
    {
        set
        {
            dbconn.ConnectionString = (value);
        }
        get
        {
            return dbconn.ConnectionString;
        }
    }
    public DataSet SelectData(string sSqlQuery)
    {
        ds = new DataSet();
        try
        {

            ds.Clear();
            SqlDataAdapter ada = new SqlDataAdapter();
            comm = new SqlCommand(sSqlQuery, dbconn);
            comm.CommandTimeout = 0;
            ada = new SqlDataAdapter(comm);
            ada.Fill(ds);
            return ds;
        }
        catch (Exception ex)
        {
            //MessageBox.Show(" Cannot Select Data " + ex.Message);
            //writeLogsOnConsole("SelectData => ","Exception: "+ex.StackTrace+"\n query: "+sSqlQuery);
            cGlobalSettings.oLogger.WriteLogException("DBFUNCTION::SelectData()" + string.Format("selecting data"), ex);
            return ds;
        }
    }

    public static string MySQLEscape(string str)
    {
        return Regex.Replace(str, @"[\x00'""\b\n\r\t\cZ\\%_]",
            delegate(Match match)
            {
                string v = match.Value;
                switch (v)
                {
                    case "\x00":            // ASCII NUL (0x00) character
                        return "\\0";
                    case "\b":              // BACKSPACE character
                        return "\\b";
                    case "\n":              // NEWLINE (linefeed) character
                        return "\\n";
                    case "\r":              // CARRIAGE RETURN character
                        return "\\r";
                    case "\t":              // TAB
                        return "\\t";
                    case "\u001A":          // Ctrl-Z
                        return "\\Z";
                    default:
                        return "\\" + v;
                }
            });
    }

    public DataSet SelectData(string sSqlQuery, out string sErrorMsg)
    {
        ds = new DataSet();
        sErrorMsg = string.Empty;

        try
        {
            ds.Clear();
            SqlDataAdapter ada = new SqlDataAdapter();
            comm = new SqlCommand(sSqlQuery, dbconn);
            comm.CommandTimeout = 0;
            ada = new SqlDataAdapter(comm);
            ada.Fill(ds);
            return ds;
        }
        catch (Exception ex)
        {
            sErrorMsg = string.Format("{0}", ex);
            cGlobalSettings.oLogger.WriteLogException("DBFUNCTION::SelectData()" + string.Format("selecting data"), ex);
            return ds;
        }
    }

    public DataSet SelectData(string sSqlQuery, string sWhere, string[] sFieldName, byte[] byFieldType, object[] sFieldValue)
    {
        ds = new DataSet();
        ds.Clear();
        string sSql = "";

        try
        {
            if (sFieldName.Length == byFieldType.Length && byFieldType.Length == sFieldValue.Length)
            {
                sSql = sSqlQuery + " where " + sWhere;
                comm = new SqlCommand(sSql, dbconn);

                for (byte byFieldCount = 0; byFieldCount < sFieldValue.Length; byFieldCount++)
                {

                    if (byFieldType[byFieldCount] == (byte)DataType.String)//varchar
                    {
                        comm.Parameters.Add("@" + sFieldName[byFieldCount], SqlDbType.NVarChar, 4000).Value = Convert.ToString(sFieldValue[byFieldCount]);
                    }
                    else if (byFieldType[byFieldCount] == (byte)DataType.StringLike)//varchar
                    {
                        comm.Parameters.Add("@" + sFieldName[byFieldCount], SqlDbType.NVarChar, 4000).Value = string.Format("%{0}%",Convert.ToString(sFieldValue[byFieldCount]));
                    }
                    else if (byFieldType[byFieldCount] == (byte)DataType.Number)//int
                    {
                        comm.Parameters.Add("@" + sFieldName[byFieldCount], SqlDbType.Int).Value = Convert.ToInt32(sFieldValue[byFieldCount]);
                    }
                    else if (byFieldType[byFieldCount] == (byte)DataType.DateTime)// datetime
                    {
                        comm.Parameters.Add("@" + sFieldName[byFieldCount], SqlDbType.DateTime).Value = Convert.ToDateTime(sFieldValue[byFieldCount]);
                    }
                }
                comm.CommandTimeout = 0;
                ada = new SqlDataAdapter(comm);
                ada.Fill(ds);
                return ds;

            }
            else
            {
                return ds;
            }
        }
        catch (Exception ex)
        {
            //MessageBox.Show(" Cannot Select Data " + ex.Message);
            cGlobalSettings.oLogger.WriteLogException("DBFUNCTION::2 SelectData()" + string.Format("selecting data"), ex);
            //writeLogsOnConsole("SelectData (2)=> ", "Exception: " + ex.StackTrace + "\n query: " + sSqlQuery);
            return ds;
        }
    }

    
    public SqlDataReader SelectDataReader(string sSqlQuery)
    {
        SqlDataReader oReader = null;
        try
        {
            comm = new SqlCommand(sSqlQuery, dbconn);
            if (dbconn.State == ConnectionState.Closed)
            {
                dbconn.Open();
            }
            oReader = comm.ExecuteReader();
            return oReader;
        }
        catch (Exception ex)
        {
            // MessageBox.Show(" Cannot Select Data " + ex.Message);
            // writeLogsOnConsole("SelectDataReader => ", "Exception: " + ex.StackTrace + "\n query: " + sSqlQuery);
            cGlobalSettings.oLogger.WriteLogException("DBFUNCTION::SelectDataReader()" + string.Format("selecting data"), ex);
            return oReader;
        }
    }


    public UInt32 getCount(string sTableName, string sFieldName)
    {
        ds = new DataSet();

        string sSql = "";
        UInt32 uiGetCount = 0;
        sSql = "SELECT Count(" + sFieldName + ") as Cnt from " + sTableName;

        SqlCommand comm = new SqlCommand(sSql, dbconn);
        SqlDataAdapter ada = new SqlDataAdapter(comm);
        ada.Fill(ds);

        if (ds.Tables[0].Rows.Count > 0)
        {
            uiGetCount = Convert.ToUInt16(ds.Tables[0].Rows[0]["Cnt"]);
        }

        ds.Clear();
        return uiGetCount;
    } // end function getcount

    // getCount with where clause
    public UInt32 getCount(string sTableName, string sFieldName, string sWhere)
    {
        ds = new DataSet();
        string sSql = "";
        UInt32 uiCount = 0;
        sSql = "SELECT Count(" + sFieldName + ") as Cnt from " + sTableName;

        if (sWhere != "")
        {
            sSql = sSql + " where " + sWhere;
        }

        comm = new SqlCommand(sSql, dbconn);
        ada = new SqlDataAdapter(comm);
        ada.Fill(ds);

        if (ds.Tables[0].Rows.Count > 0)
        {
            uiCount = Convert.ToUInt16(ds.Tables[0].Rows[0]["Cnt"]);
        }
        else
        {
            uiCount = 0;
        }

        ds.Clear();
        return uiCount;
    } // end function getcount with where clause

    //Function to get max of the field specified in table
    public UInt32 getMax(string sTableName, string sFieldName)
    {
        UInt32 uiMax = 0;
        try
        {
            string sSql;

            sSql = "SELECT Isnull(Max(" + sFieldName + "),0) as Mx from " + sTableName;

            comm = new SqlCommand(sSql, dbconn);

            if (dbconn.State == ConnectionState.Open)
            {
                dbconn.Close();
            }
            dbconn.Open();
            uiMax = Convert.ToUInt32(comm.ExecuteScalar());
            dbconn.Close();
            return uiMax;
        }
        catch (Exception ex)
        {
            if (dbconn.State == ConnectionState.Open)
            {
                dbconn.Close();
            }
            //MessageBox.Show(" Cannot get Max Id " + ex.Message);
            return uiMax;
        }


    }// end getMax function

    //Function to get max of the field specified in table
    public UInt32 getMax(string sTableName, string sFieldName, string sWhere) //get max with where clause
    {

        string sSql;
        UInt32 uiMax = 0;
        sSql = "SELECT Isnull(Max(" + sFieldName + "),0) as Mx from " + sTableName;
        if (sWhere != "")
        {
            sSql = sSql + " where " + sWhere;
        }

        comm = new SqlCommand(sSql, dbconn);
        if (dbconn.State == ConnectionState.Open)
        {
            dbconn.Close();
        }
        dbconn.Open();
        uiMax = Convert.ToUInt32(comm.ExecuteScalar());
        dbconn.Close();
        return uiMax;
    }// end getMax function with where clause



    public UInt32 getScalar(string Command)
    {
        UInt32 uiMax = 0;
        try
        {

            comm = new SqlCommand(Command, dbconn);

            if (dbconn.State == ConnectionState.Open)
            {
                dbconn.Close();
            }
            dbconn.Open();
            uiMax = Convert.ToUInt32(comm.ExecuteScalar());
            dbconn.Close();
            comm.Dispose();
            return uiMax;
        }
        catch (Exception ex)
        {
            // oUtils.WriteToFile(string.Format("getScalar({0})|error:{1}", Command, ex.Message));
            //writeLogsOnConsole("getScalar => ", "Exception: " + ex.StackTrace + "\n query: " + comm.CommandText);
            cGlobalSettings.oLogger.WriteLogException("DBFUNCTION::getScalar()" + string.Format("selecting data"), ex);
            if (dbconn.State == ConnectionState.Open)
            {
                dbconn.Close();
            }

            return uiMax;
        }


    }// end getMax function
    public string getScalarString(string Command)
    {
        string Res = string.Empty;
        try
        {

            comm = new SqlCommand(Command, dbconn);

            if (dbconn.State == ConnectionState.Open)
            {
                dbconn.Close();
            }
            dbconn.Open();
            Res = Convert.ToString(comm.ExecuteScalar());
            dbconn.Close();
            comm.Dispose();
            return Res;
        }
        catch (Exception ex)
        {
            //writeLogsOnConsole("getScalarString => ", "Exception: " + ex.StackTrace + "\n query: " + comm.CommandText);
            cGlobalSettings.oLogger.WriteLogException("DBFUNCTION::getScalarString()" + string.Format("selecting data"), ex);
            if (dbconn.State == ConnectionState.Open)
            {
                dbconn.Close();
            }
            // oUtils.WriteToFile(string.Format("getScalar({0})|error:{1}", Command, ex.Message));
            return Res;
        }


    }// end getMax function


    public UInt32 insertData(string sTableName, string[] sFieldName, byte[] sFieldType, string[] sFieldValue)
    {
        try
        {
            string sSql = "";
            string values = "";
            string fNames = "";
            for (int byFieldCount = 0; byFieldCount < sFieldValue.Length; byFieldCount++)
            {
                if (byFieldCount != sFieldValue.Length - 1)
                {
                    fNames += Convert.ToString(sFieldName[byFieldCount]) + ",";
                    values += "@" + Convert.ToString(sFieldName[byFieldCount]) + ",";
                }
                else
                {
                    fNames += Convert.ToString(sFieldName[byFieldCount]);
                    values += "@" + Convert.ToString(sFieldName[byFieldCount]);
                }
            }

            if (values != "" && values != null && sTableName != "")
            {
                sSql = "insert into " + sTableName + " (" + fNames + ") values (" + values + ")";
                comm = dbconn.CreateCommand();
                comm.CommandText = sSql;

                for (byte byFieldCount = 0; byFieldCount < sFieldValue.Length; byFieldCount++)
                {
                    if (Convert.ToString(sFieldType[byFieldCount]) == "1")//varchar
                    {
                        comm.Parameters.Add("@" + Convert.ToString(sFieldName[byFieldCount]), SqlDbType.VarChar, 5000).Value = Convert.ToString(sFieldValue[byFieldCount]);
                    }
                    else if (Convert.ToString(sFieldType[byFieldCount]) == "2")//int
                    {
                        if (sFieldValue[byFieldCount].ToString() != string.Empty)
                        {
                            comm.Parameters.Add("@" + Convert.ToString(sFieldName[byFieldCount]), SqlDbType.Float).Value = Convert.ToDouble(sFieldValue[byFieldCount]);
                        }
                        else
                        {
                            comm.Parameters.Add("@" + Convert.ToString(sFieldName[byFieldCount]), SqlDbType.Float).Value = System.DBNull.Value;
                        }
                    }
                    else if (Convert.ToString(sFieldType[byFieldCount]) == "3")// datetime
                    {
                        comm.Parameters.Add("@" + Convert.ToString(sFieldName[byFieldCount]), SqlDbType.DateTime).Value = Convert.ToDateTime(sFieldValue[byFieldCount]);
                    }

                    else if (Convert.ToString(sFieldType[byFieldCount]) == "6")// bigint
                    {
                        comm.Parameters.Add("@" + Convert.ToString(sFieldName[byFieldCount]), SqlDbType.BigInt).Value = Convert.ToInt64(sFieldValue[byFieldCount]);
                    }

                }

                if (dbconn.State == ConnectionState.Open) dbconn.Close();
                dbconn.Open();

                int result = 0;
                comm.CommandTimeout = 0;

                //debug mode handling

                result = comm.ExecuteNonQuery();

                if (isDebug)
                {
                    //debugging - will not execute queries                    
                    cGlobalSettings.oLogger.WriteLogVerbose("DBFUNCTION::insertData()" + string.Format("Inserting data"));
                }


                dbconn.Close();
                return (UInt32)result;
            }
            else
            {
                return 0;
            }
        }
        catch (Exception ex)
        {
            cGlobalSettings.oLogger.WriteLogException("DBFUNCTION::insertData()" + string.Format("Inserting data"), ex);
            //writeLogsOnConsole("insertData => ", "Exception: " + ex.StackTrace + "\n query: " + getQueriesInDetail(comm.Parameters, comm.CommandText));
            //MessageBox.Show("Error Inserting data " + ex.ToString());
            dbconn.Close();
            return 0;
        }

    }

    public UInt32 insertData(string sTableName, string[] sFieldName, byte[] sFieldType, object[] sFieldValue)
    {
        try
        {
            string sSql = "";
            string values = "";
            string fNames = "";
            for (int byFieldCount = 0; byFieldCount < sFieldValue.Length; byFieldCount++)
            {
                if (byFieldCount != sFieldValue.Length - 1)
                {
                    fNames += Convert.ToString(sFieldName[byFieldCount]) + ",";
                    values += "@" + Convert.ToString(sFieldName[byFieldCount]) + ",";
                }
                else
                {
                    fNames += Convert.ToString(sFieldName[byFieldCount]);
                    values += "@" + Convert.ToString(sFieldName[byFieldCount]);
                }
            }

            if (values != "" && values != null && sTableName != "")
            {
                sSql = "insert into " + sTableName + " (" + fNames + ") values (" + values + ")";
                comm = dbconn.CreateCommand();
                comm.CommandText = sSql;

                for (byte byFieldCount = 0; byFieldCount < sFieldValue.Length; byFieldCount++)
                {
                    if (Convert.ToString(sFieldType[byFieldCount]) == "1")//varchar
                    {
                        comm.Parameters.Add("@" + Convert.ToString(sFieldName[byFieldCount]), SqlDbType.NVarChar, 5000).Value = Convert.ToString(sFieldValue[byFieldCount]);
                    }
                    else if (Convert.ToString(sFieldType[byFieldCount]) == "2")//int
                    {
                        if (sFieldValue[byFieldCount].ToString() != string.Empty)
                        {
                            comm.Parameters.Add("@" + Convert.ToString(sFieldName[byFieldCount]), SqlDbType.Float).Value = Convert.ToDouble(sFieldValue[byFieldCount]);
                        }
                        else
                        {
                            comm.Parameters.Add("@" + Convert.ToString(sFieldName[byFieldCount]), SqlDbType.Float).Value = System.DBNull.Value;
                        }
                    }
                    else if (Convert.ToString(sFieldType[byFieldCount]) == "3")// datetime
                    {
                        comm.Parameters.Add("@" + Convert.ToString(sFieldName[byFieldCount]), SqlDbType.DateTime).Value = Convert.ToDateTime(sFieldValue[byFieldCount]);
                    }
                    else if (Convert.ToString(sFieldType[byFieldCount]) == "4")// image
                    {
                        comm.Parameters.Add("@" + Convert.ToString(sFieldName[byFieldCount]), SqlDbType.Image).Value = (byte[])(sFieldValue[byFieldCount]);
                    }

                    else if (Convert.ToInt16(sFieldType[byFieldCount]) == 5)// decimal
                    {
                        comm.Parameters.Add("@" + Convert.ToString(sFieldName[byFieldCount]), SqlDbType.Decimal).Value = Convert.ToDecimal(sFieldValue[byFieldCount]);
                    }
                    else if (Convert.ToInt16(sFieldType[byFieldCount]) == 6)// decimal
                    {
                        comm.Parameters.Add("@" + Convert.ToString(sFieldName[byFieldCount]), SqlDbType.Bit).Value = Convert.ToBoolean(sFieldValue[byFieldCount]);
                    }
                }

                if (dbconn.State == ConnectionState.Open) dbconn.Close();
                dbconn.Open();

                int result = 0;
                comm.CommandTimeout = 0;

                //debug mode handling
                result = comm.ExecuteNonQuery();

                if (isDebug)
                {
                    //debugging - will not execute queries
                    //writeLogsOnConsole("insert--", getQueriesInDetail(comm.Parameters, comm.CommandText));
                }

                dbconn.Close();
                return (UInt32)result;
            }
            else
            {
                return 0;
            }
        }
        catch (Exception ex)
        {
            cGlobalSettings.oLogger.WriteLogException("DBFUNCTION::insertData()" + string.Format("Inserting data"), ex);
            //writeLogsOnConsole("insertData (2) => ", "Exception: " + ex.StackTrace + "\n query: " + getQueriesInDetail(comm.Parameters, comm.CommandText));
            //MessageBox.Show("Error Inserting data " + ex.ToString());
            dbconn.Close();
            return 0;
        }

    }

    public int insertData(string sTableName, string[] sFieldName, byte[] sFieldType, string[] sFieldValue, string sMasterid)
    {
        try
        {
            string sSql = "";
            string values = "";
            string fNames = "";
            for (int byFieldCount = 0; byFieldCount < sFieldValue.Length; byFieldCount++)
            {
                if (byFieldCount != sFieldValue.Length - 1)
                {
                    fNames += Convert.ToString(sFieldName[byFieldCount]) + ",";
                    values += "@" + Convert.ToString(sFieldName[byFieldCount]) + ",";
                }
                else
                {
                    fNames += Convert.ToString(sFieldName[byFieldCount]);
                    values += "@" + Convert.ToString(sFieldName[byFieldCount]);
                }
            }

            if (values != "" && values != null && sTableName != "")
            {
                sSql = "insert into " + sTableName + " (" + fNames + ") OUTPUT INSERTED." + sMasterid + " values (" + values + ")";
                comm = dbconn.CreateCommand();
                comm.CommandText = sSql;

                for (byte byFieldCount = 0; byFieldCount < sFieldValue.Length; byFieldCount++)
                {
                    if (Convert.ToString(sFieldType[byFieldCount]) == "1")//varchar
                    {
                        comm.Parameters.Add("@" + Convert.ToString(sFieldName[byFieldCount]), SqlDbType.VarChar, 5000).Value = Convert.ToString(sFieldValue[byFieldCount]);
                    }
                    else if (Convert.ToString(sFieldType[byFieldCount]) == "2")//int
                    {
                        if (sFieldValue[byFieldCount].ToString() != string.Empty)
                        {
                            comm.Parameters.Add("@" + Convert.ToString(sFieldName[byFieldCount]), SqlDbType.Float).Value = Convert.ToDouble(sFieldValue[byFieldCount]);
                        }
                        else
                        {
                            comm.Parameters.Add("@" + Convert.ToString(sFieldName[byFieldCount]), SqlDbType.Float).Value = System.DBNull.Value;
                        }
                    }
                    else if (Convert.ToString(sFieldType[byFieldCount]) == "3")// datetime
                    {
                        comm.Parameters.Add("@" + Convert.ToString(sFieldName[byFieldCount]), SqlDbType.DateTime).Value = Convert.ToDateTime(sFieldValue[byFieldCount]);
                    }

                }

                if (dbconn.State == ConnectionState.Open) dbconn.Close();
                dbconn.Open();

                int result = 0;
                comm.CommandTimeout = 0;
                result = Convert.ToInt32(comm.ExecuteScalar());
                dbconn.Close();
                return (int)result;
            }
            else
            {
                return 0;
            }
        }
        catch (Exception ex)
        {
            cGlobalSettings.oLogger.WriteLogException("DBFUNCTION::insertData()" + string.Format("Inserting data"), ex);
            //MessageBox.Show("Error Inserting data " + ex.ToString());
            dbconn.Close();
            return 0;
        }

    }

    public void insertData(string sTableName, string[] sFieldName, byte[] sFieldType, object[] sFieldValue, string sMasterid, ref uint SpyId, bool getMaxId)
    {
        string sSql = string.Empty;
        string values = string.Empty;
        string fNames = string.Empty;
        // string allValues = "";
        ds = new DataSet();
        try
        {
            if (getMaxId)
            {
                fNames = sMasterid + ",";
                values = "@@" + sMasterid + ",";
            }

            for (int byFieldCount = 0; byFieldCount < sFieldValue.Length; byFieldCount++)
            {

                if (byFieldCount != sFieldValue.Length - 1)
                {
                    fNames += Convert.ToString(sFieldName[byFieldCount]) + ",";
                    values += "@" + Convert.ToString(sFieldName[byFieldCount]) + ",";
                }
                else
                {

                    fNames += Convert.ToString(sFieldName[byFieldCount]);// +"," + sMasterid;
                    values += "@" + Convert.ToString(sFieldName[byFieldCount]);// +",@" + sMasterid;

                }
            }
            if (!string.IsNullOrEmpty(values) && !string.IsNullOrEmpty(sTableName))
            {

                if (getMaxId)
                    sSql = string.Format("declare @@{0} int;select @@{0} =isnull(max({0}),0)+ 1 from {1};", sMasterid, sTableName);

                sSql += string.Format("insert into {0} ({1}) OUTPUT Inserted.{2} values ({3})", sTableName, fNames, sMasterid, values);
                comm = dbconn.CreateCommand();
                comm.CommandText = sSql;
                comm.CommandTimeout = 0;
                for (byte byFieldCount = 0; byFieldCount < sFieldValue.Length; byFieldCount++)
                {
                    //allValues += Convert.ToString(sFieldValue[byFieldCount]) + "~";
                    if (Convert.ToInt16(sFieldType[byFieldCount]) == 1)//varchar
                    {
                        comm.Parameters.Add("@" + Convert.ToString(sFieldName[byFieldCount]), SqlDbType.NVarChar, 2000).Value = Convert.ToString(sFieldValue[byFieldCount]);
                    }
                    else if (Convert.ToInt16(sFieldType[byFieldCount]) == 2)//int
                    {
                        if (sFieldValue[byFieldCount].ToString().ToLower() == "false")
                        {
                            sFieldValue[byFieldCount] = "0";
                        }
                        else if (sFieldValue[byFieldCount].ToString().ToLower() == "true")
                        {
                            sFieldValue[byFieldCount] = "1";
                        }
                        comm.Parameters.Add("@" + Convert.ToString(sFieldName[byFieldCount]), SqlDbType.Float).Value = Convert.ToDouble(sFieldValue[byFieldCount]);
                    }
                    else if (Convert.ToInt16(sFieldType[byFieldCount]) == 3)// datetime
                    {
                        comm.Parameters.Add("@" + Convert.ToString(sFieldName[byFieldCount]), SqlDbType.DateTime).Value = Convert.ToDateTime(sFieldValue[byFieldCount]);
                    }
                    else if (Convert.ToInt16(sFieldType[byFieldCount]) == 4)// byte array
                    {
                        comm.Parameters.Add("@" + Convert.ToString(sFieldName[byFieldCount]), SqlDbType.Image).Value = (byte[])(sFieldValue[byFieldCount]);
                    }
                    else if (Convert.ToInt16(sFieldType[byFieldCount]) == 5)// decimal
                    {
                        comm.Parameters.Add("@" + Convert.ToString(sFieldName[byFieldCount]), SqlDbType.Decimal).Value = Convert.ToDecimal(sFieldValue[byFieldCount]);
                    }


                }
                //  uint maxid = getMax(sTableName, sMasterid) + 1;
                // comm.Parameters.Add("@" + sMasterid, SqlDbType.Int).Value = Convert.ToInt32(maxid);

                if (dbconn.State != ConnectionState.Closed)
                {
                    dbconn.Close();
                }
                dbconn.Open();

                // int result = 0;
                //result = comm.ExecuteNonQuery();
                ada = new SqlDataAdapter(comm);
                ada.Fill(ds);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    //if (SpyId == 1)// if function is called for retrieving Main SpywareMasterId 1 means spyware_master table call
                    {
                        SpyId = Convert.ToUInt32(ds.Tables[0].Rows[0][0]);
                    }
                }
                ds.Dispose();
                /* dbconn.Close();*/


                // return (UInt32)result;
            }
            else
            {
                //return 0;
            }
        }
        catch (SqlException ex)
        {
            if (ex.ToString().ToLower().IndexOf("violation of primary key constraint") != -1)
            {
                //  dbconn.Close();
                insertData(sTableName, sFieldName, sFieldType, sFieldValue, sMasterid, ref SpyId, getMaxId);

            }
            else
            {
                cGlobalSettings.oLogger.WriteLogException("DBFUNCTION::insertData()" + string.Format("Inserting data"), ex);
                //MessageBox.Show("Error Inserting data " +  " in InsertData function with Master ID::: " + allValues + " Error " + ex.ToString() + " \n");
                // dbconn.Close();
            }

        }
        catch (Exception ex)
        {
            cGlobalSettings.oLogger.WriteLogException("DBFUNCTION::insertData()" + string.Format("Inserting data"), ex);
            //MessageBox.Show("Error Inserting data " + " in InsertData function with Master ID::: " + allValues + " Error " + ex.ToString() + " \n");
            ds.Dispose();
            // dbconn.Close();
            // return 0;
        }
        finally
        {
            sSql = values = fNames = null;
            if (ds != null)
            {
                ds = null;
            }
        }

    }

    public UInt32 updateData(string sTableName, string[] aFieldNames, byte[] aFieldTypes, string[] aFieldValues, string sWhere, string[] aWhereFieldNames, byte[] aWhereFieldTypes, string[] aWhereFieldValues)
    {
        try
        {
            //int iSyncResult = 0;
            string syncFValues = "";
            string syncFType = "";
            string sSql = "";
            // string syncWhereFNames = "";
            string syncWhereFValues = "";
            string syncWhereFType = "";
            string syncFieldNames = "";


            string sUpdateFieldNames = "";

            for (byte byFieldCount = 0; byFieldCount < aFieldValues.Length; byFieldCount++)
            {
                if (byFieldCount != aFieldValues.Length - 1)
                {
                    sUpdateFieldNames += aFieldNames[byFieldCount] + " = @" + aFieldNames[byFieldCount] + ",";
                    syncFieldNames += aFieldNames[byFieldCount] + ",";
                }
                else
                {
                    sUpdateFieldNames += aFieldNames[byFieldCount] + " = @" + aFieldNames[byFieldCount];
                    syncFieldNames += aFieldNames[byFieldCount];
                }
            }
            sSql = "UPDATE " + sTableName + " set " + sUpdateFieldNames + " where " + sWhere;
            comm = dbconn.CreateCommand();
            comm.CommandText = sSql;

            // UPDATE query updatable value parameters added
            for (byte byFieldCount = 0; byFieldCount < aFieldValues.Length; byFieldCount++)
            {
                syncFType += Convert.ToString(aFieldTypes[byFieldCount]) + ",";
                syncFValues += Convert.ToString(aFieldValues[byFieldCount]) + "~|~";

                if (aFieldTypes[byFieldCount] == (byte)DataType.String)
                {
                    comm.Parameters.Add("@" + aFieldNames[byFieldCount], SqlDbType.NText).Value = Convert.ToString(aFieldValues[byFieldCount]);

                }
                else if (aFieldTypes[byFieldCount] == (byte)DataType.Number)
                {
                    comm.Parameters.Add("@" + aFieldNames[byFieldCount], SqlDbType.Float).Value = Convert.ToDouble(aFieldValues[byFieldCount]);

                }
                else if (aFieldTypes[byFieldCount] == (byte)DataType.DateTime)
                {
                    comm.Parameters.Add("@" + aFieldNames[byFieldCount], SqlDbType.DateTime).Value = Convert.ToDateTime(aFieldValues[byFieldCount]);
                }

                else if (aFieldTypes[byFieldCount] == (byte)DataType.Decimal)// decimal
                {
                    comm.Parameters.Add("@" + aFieldNames[byFieldCount], SqlDbType.Decimal).Value = Convert.ToDecimal(aFieldValues[byFieldCount]);
                }// decimal
                else if (aFieldTypes[byFieldCount] == (byte)DataType.Bit)// Bit
                {
                    comm.Parameters.Add("@" + aFieldNames[byFieldCount], SqlDbType.Bit).Value = Convert.ToBoolean(aFieldValues[byFieldCount]);
                }


            }


            // UPDATE query where parameters added
            for (byte byFieldCount = 0; byFieldCount < aWhereFieldValues.Length; byFieldCount++)
            {
                syncWhereFType += Convert.ToString(aWhereFieldTypes[byFieldCount]) + ",";
                syncWhereFValues += aWhereFieldValues[byFieldCount] + "~|~";

                if (aWhereFieldTypes[byFieldCount] == (byte)DataType.String)
                {
                    comm.Parameters.Add("@" + aWhereFieldNames[byFieldCount], SqlDbType.NVarChar, 4000).Value = aWhereFieldValues[byFieldCount];
                    // syncWhereFNames += aWhereFieldNames[byFieldCount]+ ",";

                }
                else if (aWhereFieldTypes[byFieldCount] == (byte)DataType.Number)
                {
                    comm.Parameters.Add("@" + aWhereFieldNames[byFieldCount], SqlDbType.Float).Value = aWhereFieldValues[byFieldCount];
                    // syncWhereFNames += aWhereFieldNames[byFieldCount] + ",";

                }
                else if (aWhereFieldTypes[byFieldCount] == (byte)DataType.DateTime)
                {
                    comm.Parameters.Add("@" + aWhereFieldNames[byFieldCount], SqlDbType.DateTime).Value = aWhereFieldValues[byFieldCount];
                    // syncWhereFNames += aWhereFieldNames[byFieldCount] + ",";


                }
                else if (aWhereFieldTypes[byFieldCount] == (byte)DataType.Bit)
                {
                    comm.Parameters.Add("@" + aWhereFieldNames[byFieldCount], SqlDbType.Bit).Value = aWhereFieldValues[byFieldCount];
                }
            }

            if (sTableName != "")
            {
                if (dbconn.State != ConnectionState.Closed)
                {
                    dbconn.Close();
                }
                dbconn.Open();
                int result = 0;

                //debug mode handling
                result = comm.ExecuteNonQuery();
                if (isDebug)
                {
                    //debugging - will not execute queries
                    //writeLogsOnConsole("update--", getQueriesInDetail(comm.Parameters, comm.CommandText));
                }

                dbconn.Close();





                return (UInt32)result;



            }
            else
            {
                return 0;
            }
        }
        catch (Exception ex)
        {
            cGlobalSettings.oLogger.WriteLogException("DBFUNCTION::updateData()" + string.Format("updateData data"), ex);
            //writeLogsOnConsole("update => ", "Exception: " + ex.StackTrace + "\n query: " + getQueriesInDetail(comm.Parameters, comm.CommandText));
            return 0;
        }
    }


    public UInt32 updateData(string sTableName, string[] aFieldNames, byte[] aFieldTypes, string[] aFieldValues, string sWhere, string[] aWhereFieldNames, byte[] aWhereFieldTypes, string[] aWhereFieldValues, string updateByOperator)
    {
        try
        {
            //int iSyncResult = 0;
            string syncFValues = "";
            string syncFType = "";
            string sSql = "";
            // string syncWhereFNames = "";
            string syncWhereFValues = "";
            string syncWhereFType = "";
            string syncFieldNames = "";


            string sUpdateFieldNames = "";

            for (byte byFieldCount = 0; byFieldCount < aFieldValues.Length; byFieldCount++)
            {
                if (byFieldCount != aFieldValues.Length - 1)
                {
                    if (updateByOperator == "+" || updateByOperator == "-")
                    {
                        sUpdateFieldNames += aFieldNames[byFieldCount] + " = " + aFieldNames[byFieldCount] + " " + updateByOperator + " @" + aFieldNames[byFieldCount] + ",";
                        syncFieldNames += aFieldNames[byFieldCount] + ",";
                    }
                    else
                    {
                        sUpdateFieldNames += aFieldNames[byFieldCount] + " = @" + aFieldNames[byFieldCount] + ",";
                        syncFieldNames += aFieldNames[byFieldCount] + ",";
                    }
                }
                else
                {
                    if (updateByOperator == "+" || updateByOperator == "-")
                    {
                        sUpdateFieldNames += aFieldNames[byFieldCount] + " = " + aFieldNames[byFieldCount] + " " + updateByOperator + " @" + aFieldNames[byFieldCount];
                        syncFieldNames += aFieldNames[byFieldCount];
                    }
                    else
                    {
                        sUpdateFieldNames += aFieldNames[byFieldCount] + " = @" + aFieldNames[byFieldCount];
                        syncFieldNames += aFieldNames[byFieldCount];
                    }
                }
            }
            sSql = "UPDATE " + sTableName + " set " + sUpdateFieldNames + " where " + sWhere;
            comm = dbconn.CreateCommand();
            comm.CommandText = sSql;

            // UPDATE query updatable value parameters added
            for (byte byFieldCount = 0; byFieldCount < aFieldValues.Length; byFieldCount++)
            {
                syncFType += Convert.ToString(aFieldTypes[byFieldCount]) + ",";
                syncFValues += Convert.ToString(aFieldValues[byFieldCount]) + "~|~";

                if (aFieldTypes[byFieldCount] == (byte)DataType.String)
                {
                    comm.Parameters.Add("@" + aFieldNames[byFieldCount], SqlDbType.NText).Value = Convert.ToString(aFieldValues[byFieldCount]);

                }
                else if (aFieldTypes[byFieldCount] == (byte)DataType.Number)
                {
                    comm.Parameters.Add("@" + aFieldNames[byFieldCount], SqlDbType.Float).Value = Convert.ToDouble(aFieldValues[byFieldCount]);

                }
                else if (aFieldTypes[byFieldCount] == (byte)DataType.DateTime)
                {
                    comm.Parameters.Add("@" + aFieldNames[byFieldCount], SqlDbType.DateTime).Value = Convert.ToDateTime(aFieldValues[byFieldCount]);
                }

                else if (aFieldTypes[byFieldCount] == (byte)DataType.Decimal)// decimal
                {
                    comm.Parameters.Add("@" + aFieldNames[byFieldCount], SqlDbType.Decimal).Value = Convert.ToDecimal(aFieldValues[byFieldCount]);
                }// decimal


            }


            // UPDATE query where parameters added
            for (byte byFieldCount = 0; byFieldCount < aWhereFieldValues.Length; byFieldCount++)
            {
                syncWhereFType += Convert.ToString(aWhereFieldTypes[byFieldCount]) + ",";
                syncWhereFValues += aWhereFieldValues[byFieldCount] + "~|~";

                if (aWhereFieldTypes[byFieldCount] == (byte)DataType.String)
                {
                    comm.Parameters.Add("@" + aWhereFieldNames[byFieldCount], SqlDbType.NVarChar, 4000).Value = aWhereFieldValues[byFieldCount];
                    // syncWhereFNames += aWhereFieldNames[byFieldCount]+ ",";

                }
                else if (aWhereFieldTypes[byFieldCount] == (byte)DataType.Number)
                {
                    comm.Parameters.Add("@" + aWhereFieldNames[byFieldCount], SqlDbType.Float).Value = aWhereFieldValues[byFieldCount];
                    // syncWhereFNames += aWhereFieldNames[byFieldCount] + ",";

                }
                else if (aWhereFieldTypes[byFieldCount] == (byte)DataType.DateTime)
                {
                    comm.Parameters.Add("@" + aWhereFieldNames[byFieldCount], SqlDbType.DateTime).Value = aWhereFieldValues[byFieldCount];
                    // syncWhereFNames += aWhereFieldNames[byFieldCount] + ",";


                }
            }

            if (sTableName != "")
            {
                if (dbconn.State != ConnectionState.Closed)
                {
                    dbconn.Close();
                }
                dbconn.Open();
                int result = 0;

                //debug mode handling
                result = comm.ExecuteNonQuery();
                if (isDebug)
                {
                    //debugging - will not execute queries
                    //writeLogsOnConsole("update--", getQueriesInDetail(comm.Parameters, comm.CommandText));
                }
                dbconn.Close();





                return (UInt32)result;



            }
            else
            {
                return 0;
            }
        }
        catch (Exception ex)
        {
            cGlobalSettings.oLogger.WriteLogException("DBFUNCTION::updateData()" + string.Format("updateData data"), ex);
            //writeLogsOnConsole("update (2) => ", "Exception: " + ex.StackTrace + "\n query: " + getQueriesInDetail(comm.Parameters, comm.CommandText));
            return 0;
        }
    }

    public UInt32 deleteData(string sTableName, string sWhere, string[] aFieldNames, byte[] aFieldTypes, string[] aFieldValues)
    {
        try
        {
            string sSql = "";
            sSql = "DELETE from " + sTableName + " where " + sWhere;
            comm = dbconn.CreateCommand();
            comm.CommandText = sSql;
            for (byte byFieldCount = 0; byFieldCount < aFieldValues.Length; byFieldCount++)
            {
                if (aFieldTypes[byFieldCount] == (byte)DataType.String)//varchar
                {
                    comm.Parameters.Add("@" + Convert.ToString(aFieldNames[byFieldCount]), SqlDbType.VarChar, 4000).Value = Convert.ToString(aFieldValues[byFieldCount]);
                }
                else if (aFieldTypes[byFieldCount] == (byte)DataType.Number)//int
                {
                    comm.Parameters.Add("@" + Convert.ToString(aFieldNames[byFieldCount]), SqlDbType.Int).Value = Convert.ToInt32(aFieldValues[byFieldCount]);
                }
                else if (aFieldTypes[byFieldCount] == (byte)DataType.DateTime)// datetime
                {
                    comm.Parameters.Add("@" + Convert.ToString(aFieldNames[byFieldCount]), SqlDbType.DateTime).Value = Convert.ToDateTime(aFieldValues[byFieldCount]);
                }
            }

            if (sTableName != "")
            {
                if (dbconn.State != ConnectionState.Closed)
                {
                    dbconn.Close();
                }
                dbconn.Open();
                int result = 0;

                //debug mode handling

                result = comm.ExecuteNonQuery();

                if (isDebug)
                {
                    //debugging - will not execute queries
                    //writeLogsOnConsole("delete--", getQueriesInDetail(comm.Parameters, comm.CommandText));
                }

                dbconn.Close();
                return (UInt32)result;
            }
            else
            {
                return 0;
            }
        }
        catch (Exception ex)
        {
            cGlobalSettings.oLogger.WriteLogException("DBFUNCTION::deletedata()" + string.Format("deletedata data"), ex);
            //writeLogsOnConsole("delete  => ", "Exception: " + ex.StackTrace + "\n query: " + getQueriesInDetail(comm.Parameters, comm.CommandText));
            return 0;
        }
    } // End of Function
    ////////

    public UInt32 updateDataQuery(string sQuery)
    {
        try
        {
            if (sQuery != "")
            {
                comm = dbconn.CreateCommand();
                comm.CommandText = sQuery;
                comm.CommandTimeout = 0;
                if (dbconn.State != ConnectionState.Closed)
                {
                    dbconn.Close();
                }
                dbconn.Open();
                int result = 0;

                //debug mode handling
                result = comm.ExecuteNonQuery();
                if (isDebug)
                {
                    //debugging - will not execute queries
                    // writeLogsOnConsole("delete--", sQuery);
                }


                dbconn.Close();
                return (UInt32)result;
            }
            else
            {

                return 0;
            }
        }
        catch (Exception ex)
        {
            cGlobalSettings.oLogger.WriteLogException("DBFUNCTION::updateDataQuery()" + string.Format("deletedata data"), ex);
            return 0;
        }
    } // End of Function

    public UInt32 deleteData(string sQuery)
    {
        try
        {
            if (sQuery != "")
            {
                comm = dbconn.CreateCommand();
                comm.CommandText = sQuery;
                comm.CommandTimeout = 0;
                if (dbconn.State != ConnectionState.Closed)
                {
                    dbconn.Close();
                }
                dbconn.Open();
                int result = 0;

                //debug mode handling
                result = comm.ExecuteNonQuery();
                if (isDebug)
                {
                    //debugging - will not execute queries
                    // writeLogsOnConsole("delete--", sQuery);
                }


                dbconn.Close();
                return (UInt32)result;
            }
            else
            {

                return 0;
            }
        }
        catch (Exception ex)
        {
            cGlobalSettings.oLogger.WriteLogException("DBFUNCTION::deletedata()" + string.Format("deletedata data"), ex);
            return 0;
        }
    } // End of Function


    // Returns date time in format YYYY-MM-DD HH:MM:SS 
    public DateTime getDateTime()
    {
        ds = SelectData("SELECT Convert(varchar,getdate(), 120) As Date");
        return Convert.ToDateTime(ds.Tables[0].Rows[0]["Date"]);
    }

    // Returns date time in SPECIFIED format
    public DateTime getDateTime(byte byDateFormat)
    {
        try
        {
            ds = SelectData("SELECT Convert(varchar,getdate(), " + byDateFormat + ") As Date");
            return Convert.ToDateTime(ds.Tables[0].Rows[0]["Date"]);
        }
        catch (SqlException)
        {
            //oErrorHandle.ShowDatabaseMessageBox(ErrorHandle.DatabaseOperation.InvalidDateFormat);
            return DateTime.Now;
        }
    }

    // Returns the Master Code with the specified Prefix as per table
    public string getMasterCode(string sPrifix, string sTableName, string sColumnName)
    {
        byte byDefaultCodeNumberLength = 4;
        UInt32 uiMaxCode = (UInt32)(getMax(sTableName, sColumnName) + 1);
        sPrifix = sPrifix.ToUpper();

        if (uiMaxCode.ToString().Length < byDefaultCodeNumberLength)
        {
            if (uiMaxCode.ToString().Length == 1)
            {
                return sPrifix + "-000" + uiMaxCode;
            }
            else if (uiMaxCode.ToString().Length == 2)
            {
                return sPrifix + "-00" + uiMaxCode;
            }
            else if (uiMaxCode.ToString().Length == 3)
            {
                return sPrifix + "-0" + uiMaxCode;
            }
            else
            {
                return sPrifix + "-" + uiMaxCode;
            }
        }
        else
        {
            return sPrifix + "-" + uiMaxCode;
        }
    }





    //end function

    public bool LockedCheck(string sTableName, UInt32 uiMasterId)
    {
        bool bResult = false;
        DataSet ds = new DataSet();
        ds = SelectData("SELECT isLocked from " + sTableName + " where " + sTableName + "Id=" + uiMasterId);
        if (ds.Tables[0].Rows.Count > 0)
        {
            if (ds.Tables[0].Rows[0]["islocked"].ToString() == "Y")
            {
                bResult = true;
            }
            else
            {
                bResult = false;
            }

        }
        return bResult;
    }// end function



    public string checkEnvironmentVar(string val)
    {
        string sEnvironmentVar1 = "%ALLUSERSPROFILE%";
        string sEnvironmentVar2 = "%PROGRAMFILES%";
        string tempDir = val;
        string sDirResult = val.ToLower();

        if (tempDir != "")
        {


            if (sDirResult.ToLower().IndexOf(@"c:\documents and settings\all users") != -1)
            {

                sDirResult = Regex.Replace(sDirResult, @"c:\\documents and settings\\all users", sEnvironmentVar1.ToUpper());
            }
            if (sDirResult.ToLower().IndexOf(@"c:\program files") != -1)
            {
                sDirResult = Regex.Replace(sDirResult, @"c:\\program files", sEnvironmentVar2.ToUpper());
            }


            if (sDirResult.IndexOf(@"d:\documents and settings\all users") != -1)
            {
                sDirResult = Regex.Replace(sDirResult, @"d:\\documents and settings\\all users", sEnvironmentVar1);
            }
            if (sDirResult.IndexOf(@"d:\program files") != -1)
            {
                sDirResult = Regex.Replace(sDirResult, @"d:\\program files", sEnvironmentVar2);
            }


            if (sDirResult.IndexOf(@"e:\documents and settings\all users") != -1)
            {
                sDirResult = Regex.Replace(sDirResult, @"e:\\documents and settings\\all users", sEnvironmentVar1);
            }
            if (sDirResult.IndexOf(@"e:\program files") != -1)
            {
                sDirResult = Regex.Replace(sDirResult, @"e:\\program files", sEnvironmentVar2);
            }
        }
        else
        {
            sDirResult = tempDir;
        }
        return sDirResult;
    }// end function//



    public string plainHtml(string sStyleHtml)
    {
        //sStyleHtml = Regex.Replace(sStyleHtml.ToLower(), @"<table(.*?)>", "<table>");
        //sStyleHtml = Regex.Replace(sStyleHtml.ToLower(), @"<tr(.*?)>", "<tr>");
        //sStyleHtml = Regex.Replace(sStyleHtml.ToLower(), @"<td(.*?)>", "<td>");
        //sStyleHtml = Regex.Replace(sStyleHtml.ToLower(), @"<font(.*?)>", "");
        //sStyleHtml = Regex.Replace(sStyleHtml.ToLower(), @"</font>", "");
        //sStyleHtml = Regex.Replace(sStyleHtml, @"<p(.*?)>", "",RegexOptions.IgnoreCase);
        //sStyleHtml = Regex.Replace(sStyleHtml, @"</p>", "",RegexOptions.IgnoreCase);
        return sStyleHtml;

    }



    public object SelectScalar(string sQuery)
    {
        try
        {
            comm = new SqlCommand(sQuery, dbconn);

            if (dbconn.State == ConnectionState.Open)
            {
                dbconn.Close();
            }
            dbconn.Open();

            object result = null;
            //debug mode handling
            result = (comm.ExecuteScalar());

            if (isDebug)
            {
                //debugging - will not execute queries
                //writeLogsOnConsole("selectScalar--", sQuery);
            }


            dbconn.Close();
            if (result == null)
            {
                return 0;
            }
            else
            {
                return result;
            }

        }
        catch (Exception ex)
        {
            cGlobalSettings.oLogger.WriteLogException("DBFUNCTION::SelectScalar()" + string.Format("SelectScalar data"), ex);
            //writeLogsOnConsole("SelectScalar  => ", "Exception: " + ex.StackTrace + "\n query: " + sQuery);
            //MessageBox.Show("SelectScalar(): " + ex.ToString(), "Alert", MessageBoxButtons.OK, MessageBoxIcon.Information);
            //oGlobal.WriteToFile("SelectData<Q, T>()", sQuery + " : " + obex.ToString());
            return 0;
        }
    }

    public Int32 CountUserRecords(Int32 startIndex, Int32 pageSize, String sortExpression)
    {
        Int32 totalRecords = 0;
        try
        {
            comm = new SqlCommand("sp_UserRecordCount", dbconn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandTimeout = 0;
            dbconn.Open();
            oDataReader = comm.ExecuteReader();
            oDataReader.Read();
            if (oDataReader.HasRows)
                totalRecords = Convert.ToInt32(oDataReader["Count"]);
        }
        catch (System.Exception ex)
        {

        }
        return totalRecords;
    }
    public DataSet UserRecords(Int32 startIndex, Int32 pageSize, String sortExpression)
    {
        ds = new DataSet();
        try
        {
            ds.Clear();

            //SqlDataAdapter ada = new SqlDataAdapter();
            comm = new SqlCommand("sp_UserRecord", dbconn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@PageSize", pageSize);
            comm.Parameters["@PageSize"].Direction = ParameterDirection.Input;
            comm.Parameters.AddWithValue("@CurrentPage", startIndex);
            comm.Parameters["@CurrentPage"].Direction = ParameterDirection.Input;
            comm.Parameters.AddWithValue("@SortExpression", sortExpression);
            comm.Parameters["@SortExpression"].Direction = ParameterDirection.Input;

            comm.CommandTimeout = 0;

            ada = new SqlDataAdapter(comm);
            //ada.SelectCommand = comm;
            ada.Fill(ds);
            return ds;
        }
        catch (Exception ex)
        {
            return ds;
        }
    }


    public int PlanCount(int startIndex, int pageSize, string sortExpression, int IsActive)
    {
        Int32 totalRecords = 0;
        try
        {
            comm = new SqlCommand("sp_GetPlanCount", dbconn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@IsActive", IsActive);
            comm.Parameters["@IsActive"].Direction = ParameterDirection.Input;
            comm.CommandTimeout = 0;
            dbconn.Open();
            oDataReader = comm.ExecuteReader();
            oDataReader.Read();
            if (oDataReader.HasRows)
                totalRecords = Convert.ToInt32(oDataReader["Count"]);
        }
        catch (System.Exception ex)
        {

        }
        return totalRecords;
    }
    public DataSet Plan(Int32 startIndex, Int32 pageSize, String sortExpression, int IsActive)
    {
        ds = new DataSet();
        try
        {
            ds.Clear();
            comm = new SqlCommand("sp_GetPlan", dbconn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@PageSize", pageSize);
            comm.Parameters["@PageSize"].Direction = ParameterDirection.Input;
            comm.Parameters.AddWithValue("@CurrentPage", startIndex);
            comm.Parameters["@CurrentPage"].Direction = ParameterDirection.Input;
            comm.Parameters.AddWithValue("@SortExpression", sortExpression);
            comm.Parameters["@SortExpression"].Direction = ParameterDirection.Input;
            comm.Parameters.AddWithValue("@IsActive", IsActive);
            comm.Parameters["@IsActive"].Direction = ParameterDirection.Input;
            comm.CommandTimeout = 0;

            ada = new SqlDataAdapter(comm);
            //ada.SelectCommand = comm;
            ada.Fill(ds);
            return ds;
        }
        catch (Exception ex)
        {
            return ds;
        }
    }


    public Int32 DeviceRecordsCount(Int32 UserId, Int32 startIndex, Int32 pageSize, String sortExpression)
    {
        Int32 totalRecords = 0;
        try
        {
            comm = new SqlCommand("sp_DeviceDetailCount", dbconn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandTimeout = 0;
            dbconn.Open();
            oDataReader = comm.ExecuteReader();
            oDataReader.Read();
            if (oDataReader.HasRows)
                totalRecords = Convert.ToInt32(oDataReader["Count"]);
        }
        catch (System.Exception ex)
        {

        }
        return totalRecords;
    }
    public DataSet DeviceRecords(Int32 UserId, Int32 startIndex, Int32 pageSize, String sortExpression)
    {
        ds = new DataSet();
        try
        {
            ds.Clear();

            //SqlDataAdapter ada = new SqlDataAdapter();
            comm = new SqlCommand("sp_DeviceDetail", dbconn);
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@UserId", pageSize);
            comm.Parameters["@UserId"].Direction = ParameterDirection.Input;
            comm.Parameters.AddWithValue("@PageSize", pageSize);
            comm.Parameters["@PageSize"].Direction = ParameterDirection.Input;
            comm.Parameters.AddWithValue("@CurrentPage", startIndex);
            comm.Parameters["@CurrentPage"].Direction = ParameterDirection.Input;
            comm.Parameters.AddWithValue("@SortExpression", sortExpression);
            comm.Parameters["@SortExpression"].Direction = ParameterDirection.Input;
            comm.CommandTimeout = 0;

            ada = new SqlDataAdapter(comm);
            //ada.SelectCommand = comm;
            ada.Fill(ds);
            return ds;
        }
        catch (Exception ex)
        {
            return ds;
        }
    }

    //error log
    public void writeLogsOnConsole(string title, string msg)
    {
        try
        {
            string filenameprefix = "DB_query_";
            string fileName = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("../errortraces/"), filenameprefix + DateTime.Today.Day + "_" + DateTime.Today.Month + "_" + DateTime.Today.Year + "_log.txt");
            // Store the script names and test results in a output text file.
            using (StreamWriter writer = new StreamWriter(new FileStream(fileName, FileMode.Append)))
            {
                writer.WriteLine("\n\n ***************Entry At: " + DateTime.Now.ToShortTimeString() + " ****************\n\n" + "{0}{1}", title, "\n ---- \n" + msg);
            }
        }
        catch (Exception ex)
        {

        }
    }

    //show parameterized query
    public string getQueriesInDetail(SqlParameterCollection cp, string query)
    {
        foreach (SqlParameter p in cp)
        {
            query = query.Replace(p.ParameterName, p.Value.ToString());
        }

        return query;
    }


    public static int userRecordsCount = 0;

    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public DataSet GetUserRecords(int startRowIndex, int pageSize, string SortExpression, string searchText, int UserId, int IssueStatusId)
    {
        int totalUsers = 0;
        if (string.IsNullOrEmpty(SortExpression))
            SortExpression = "DateOfIssue DESC";

        DataSet result = GetUserRecord(startRowIndex, pageSize, SortExpression, ref totalUsers, searchText, UserId, IssueStatusId);
        userRecordsCount = totalUsers;
        if (result.Tables.Count > 0)
            if (result.Tables[0].Rows.Count > 0)
                return result;

        return null;
    }

    [DataObjectMethod(DataObjectMethodType.Select, false)]
    public static int GetUserRecordCount(int startRowIndex, int pageSize, string SortExpression, string searchText, int UserId, int IssueStatusId)
    {
        return userRecordsCount;
    }

    public DataSet GetUserRecord(int startRowIndex, int pageSize, string sortExpression, ref int totalUsers, string searchText, int UserId, int IssueStatusId)
    {
        startRowIndex = Convert.ToInt32(startRowIndex / pageSize) + 1;
        SqlCommand selectCommand = new SqlCommand();
        selectCommand.CommandText = "dbo.[BetaUserDetails]";
        selectCommand.Connection = dbconn;
        selectCommand.CommandType = CommandType.StoredProcedure;
        selectCommand.Parameters.Add(new SqlParameter("@startRowIndex", SqlDbType.Int));
        selectCommand.Parameters[0].Value = startRowIndex;
        selectCommand.Parameters.Add(new SqlParameter("@pageSize", SqlDbType.Int));
        selectCommand.Parameters[1].Value = pageSize;
        selectCommand.Parameters.Add(new SqlParameter("@SortExpression", SqlDbType.NVarChar, 50));
        selectCommand.Parameters[2].Value = sortExpression;
        selectCommand.Parameters.Add(new SqlParameter("@SearchText", SqlDbType.NVarChar, 2000));
        selectCommand.Parameters[3].Value = searchText;
        selectCommand.Parameters.Add(new SqlParameter("@UserId", SqlDbType.Int));
        selectCommand.Parameters[4].Value = UserId;
        selectCommand.Parameters.Add(new SqlParameter("@IssueStatusId", SqlDbType.Int));
        selectCommand.Parameters[5].Value = IssueStatusId;
        selectCommand.Parameters.Add(new SqlParameter("@TotalCount", SqlDbType.Int));
        selectCommand.Parameters[6].Value = totalUsers;
        ((System.Data.SqlClient.SqlParameter)selectCommand.Parameters["@TotalCount"]).Direction = ParameterDirection.Output;
        SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        totalUsers = Convert.ToInt32(((System.Data.SqlClient.SqlParameter)selectCommand.Parameters["@TotalCount"]).Value);

        return ds;
    }


    public static int adminRecordsCount = 0;

    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public DataSet GetAdminRecords(int startRowIndex, int pageSize, string SortExpression, string searchText, int RoleId, int UserId)
    {
        int totalUsers = 0;
        if (string.IsNullOrEmpty(SortExpression))
            SortExpression = "DateOfIssue DESC";

        DataSet result = GetAdminRecord(startRowIndex, pageSize, SortExpression, ref totalUsers, searchText, RoleId, UserId);
        secAdminRecordsCount = totalUsers;
        if (result.Tables.Count > 0)
            if (result.Tables[0].Rows.Count > 0)
                return result;

        return null;
    }

    [DataObjectMethod(DataObjectMethodType.Select, false)]
    public static int GetAdminRecordCount(int startRowIndex, int pageSize, string SortExpression, string searchText, int RoleId, int UserId)
    {
        return secAdminRecordsCount;
    }

    public DataSet GetAdminRecord(int startRowIndex, int pageSize, string sortExpression, ref int totalUsers, string searchText, int RoleId, int UserId)
    {
        startRowIndex = Convert.ToInt32(startRowIndex / pageSize) + 1;
        SqlCommand selectCommand = new SqlCommand();
        selectCommand.CommandText = "dbo.[BetaAdminAssignIssues]";
        selectCommand.Connection = dbconn;
        selectCommand.CommandType = CommandType.StoredProcedure;
        selectCommand.Parameters.Add(new SqlParameter("@startRowIndex", SqlDbType.Int));
        selectCommand.Parameters[0].Value = startRowIndex;
        selectCommand.Parameters.Add(new SqlParameter("@pageSize", SqlDbType.Int));
        selectCommand.Parameters[1].Value = pageSize;
        selectCommand.Parameters.Add(new SqlParameter("@SortExpression", SqlDbType.NVarChar, 50));
        selectCommand.Parameters[2].Value = sortExpression;
        selectCommand.Parameters.Add(new SqlParameter("@SearchText", SqlDbType.NVarChar, 2000));
        selectCommand.Parameters[3].Value = searchText;
        selectCommand.Parameters.Add(new SqlParameter("@RoleId", SqlDbType.Int));
        selectCommand.Parameters[4].Value = RoleId;
        selectCommand.Parameters.Add(new SqlParameter("@UserId", SqlDbType.Int));
        selectCommand.Parameters[5].Value = UserId;
        selectCommand.Parameters.Add(new SqlParameter("@TotalCount", SqlDbType.Int));
        selectCommand.Parameters[6].Value = totalUsers;
        ((System.Data.SqlClient.SqlParameter)selectCommand.Parameters["@TotalCount"]).Direction = ParameterDirection.Output;
        SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        totalUsers = Convert.ToInt32(((System.Data.SqlClient.SqlParameter)selectCommand.Parameters["@TotalCount"]).Value != DBNull.Value ? ((System.Data.SqlClient.SqlParameter)selectCommand.Parameters["@TotalCount"]).Value : "0");
        //totalUsers = Convert.ToInt32(((System.Data.SqlClient.SqlParameter)selectCommand.Parameters["@TotalCount"]).Value);

        return ds;
    }

    public static int secAdminRecordsCount = 0;

    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public DataSet GetSecAdminRecords(int startRowIndex, int pageSize, string SortExpression, string searchText, int UserId, int AssignUserId)
    {
        if (AssignUserId != 0)
            UserId = 0;

        int totalUsers = 0;
        if (string.IsNullOrEmpty(SortExpression))
            SortExpression = "DateOfIssue DESC";

        DataSet result = GetSecAdminRecord(startRowIndex, pageSize, SortExpression, ref totalUsers, searchText, UserId, AssignUserId);
        secAdminRecordsCount = totalUsers;
        if (result.Tables.Count > 0)
            if (result.Tables[0].Rows.Count > 0)
                return result;

        return null;
    }

    [DataObjectMethod(DataObjectMethodType.Select, false)]
    public static int GetSecAdminRecordCount(int startRowIndex, int pageSize, string SortExpression, string searchText, int UserId, int AssignUserId)
    {
        return secAdminRecordsCount;
    }

    public DataSet GetSecAdminRecord(int startRowIndex, int pageSize, string sortExpression, ref int totalUsers, string searchText, int UserId, int AssignUserId)
    {
        startRowIndex = Convert.ToInt32(startRowIndex / pageSize) + 1;
        SqlCommand selectCommand = new SqlCommand();
        selectCommand.CommandText = "dbo.[BetaAdminIssues]";
        selectCommand.Connection = dbconn;
        selectCommand.CommandType = CommandType.StoredProcedure;
        selectCommand.Parameters.Add(new SqlParameter("@startRowIndex", SqlDbType.Int));
        selectCommand.Parameters[0].Value = startRowIndex;
        selectCommand.Parameters.Add(new SqlParameter("@pageSize", SqlDbType.Int));
        selectCommand.Parameters[1].Value = pageSize;
        selectCommand.Parameters.Add(new SqlParameter("@SortExpression", SqlDbType.NVarChar, 50));
        selectCommand.Parameters[2].Value = sortExpression;
        selectCommand.Parameters.Add(new SqlParameter("@SearchText", SqlDbType.NVarChar, 2000));
        selectCommand.Parameters[3].Value = searchText;
        selectCommand.Parameters.Add(new SqlParameter("@AssignedToUserId", SqlDbType.Int));
        if (UserId == 0)
            selectCommand.Parameters[4].Value = AssignUserId;
        else
            selectCommand.Parameters[4].Value = UserId;
        selectCommand.Parameters.Add(new SqlParameter("@TotalCount", SqlDbType.Int));
        selectCommand.Parameters[5].Value = totalUsers;
        ((System.Data.SqlClient.SqlParameter)selectCommand.Parameters["@TotalCount"]).Direction = ParameterDirection.Output;
        SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        totalUsers = Convert.ToInt32(((System.Data.SqlClient.SqlParameter)selectCommand.Parameters["@TotalCount"]).Value);

        return ds;
    }

    public void updateRecords(string IssueId, string UserId)
    {
        deleteData(string.Format("update BetaIssueAssignment set AssignedToUserID={0}, AssignmentStartDateTime='{2}' where IssueId={1}", UserId, IssueId, DateTime.UtcNow));
    }


    [DataObjectMethod(DataObjectMethodType.Select, true)]
    public DataSet GetAdminDashBoardRecords(string sortExpression)
    {
        if (string.IsNullOrEmpty(sortExpression))
            sortExpression = "UserName";

        DataSet result = GetAdminDashBoardRecord(sortExpression);
        if (result.Tables.Count > 0)
            if (result.Tables[0].Rows.Count > 0)
                return result;

        return null;
    }

    public DataSet GetAdminDashBoardRecord(string sortExpression)
    {
        SqlCommand selectCommand = new SqlCommand();
        selectCommand.CommandText = "dbo.[BetaAdminDashBoard]";
        selectCommand.Connection = dbconn;
        selectCommand.CommandType = CommandType.StoredProcedure;
        selectCommand.Parameters.Add(new SqlParameter("@sortExpression", SqlDbType.NVarChar, 50));
        selectCommand.Parameters[0].Value = sortExpression;
        SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        return ds;
    }
}
